/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controller;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import model.Content;

import command.CommandParser;

import config.Debug;
import config.Helper;
import config.Settings;
import data.CSVToMysqlConverter;
import data.Downloader;
import data.HttpRequest;
import data.SQLFileParser;
import data.Unzipper;

/**
 * 
 * @author Yang Wang <yang@digitalmenubox.com>
 */
public class Initializer {

	public static void initializePlayer() {
		/* Create player database if it does not exist */
		createDirs();
		Command.moveMouseToRightSide();
		createPlayerDatabase();
		if (Settings.isBlankPlayer()) {
			Command.resetPlayer();
			Debug.log("Player is reset to default.", Debug.SYSTEM_LEVEL);
			//HttpRequest.resendContent();
		}

		// don't really need to download things when the player starts
		/*
		 * boolean downloaded = false; if (Helper.hasNetworkConnection() &&
		 * UpdateChecker.hasNewUpdate()) { downloaded = downloadUpdate(true);
		 * //downloaded = true; } else { //no updates available; }
		 * 
		 * // loop through the sql files and update the database according to
		 * the csv files executeSqlScriptsFromFolder();
		 * 
		 * //loop through the csv files and update the database according to the
		 * csv files inputCsvFilesToDatabase();
		 * 
		 * // delete the downloaded resource file File resourceFile = new
		 * File(Settings.getResourcesFileName()); if (resourceFile.isFile()) {
		 * resourceFile.delete(); }
		 * 
		 * if (Helper.hasNetworkConnection()) { //downloads contents one by one
		 * //downloadContents(); }
		 */

		Settings.setBlankPlayer(false);
		Settings.save();
	}

	public static synchronized boolean downloadUpdate(boolean showProgressBar) {
		/* Download the update file using ftp or http */
		boolean downloaded = false;
		new File(Settings.getResourcesFileName()).delete();
		Downloader downloader = Downloader.createDownloader();
		downloader.setShowProgressWindow(showProgressBar);
		downloaded = downloader.download(Settings.getHtmlResourceFileLocation(), Settings.getResourcesFileName(), Downloader.DIGITAL_MARKETING_BOX_SERVER);
		File resourceFile = new File(Settings.getResourcesFileName());
		if (resourceFile.exists()) {
			Unzipper.unzip(resourceFile, new File("."));
		}
		if (downloaded) {
			HttpRequest.postUpdateComplete();
		} else {
			Debug.log("Update was not downloaded successfully");
		}
		return downloaded;

	}

	/*
	 * create database tables if the tables do not exist
	 */
	public static void createPlayerDatabase() {
		// System.out.println("Create Player Database is called");
		Connection conn = null;
		Statement s = null;
		try {
			String url = "jdbc:mysql://" + Settings.getLocalMysqlServer() + "/?user=" + Settings.getLocalMysqlUsername() + "&password="
					+ Settings.getLocalMysqlPassword();
			conn = DriverManager.getConnection(url);
			s = conn.createStatement();
			// s.execute("DROP DATABASE IF EXISTS `player_db`;");
			s.execute("CREATE DATABASE IF NOT EXISTS " + Settings.getLocalMysqlDatabase() + ";");

			InputStream is = ScheduleController.class.getClassLoader().getResourceAsStream("appdata/player_db.sql");
			if (is != null) {
				InputStreamReader isr = new InputStreamReader(is);
				BufferedReader br = new BufferedReader(isr);
				StringBuilder sb = new StringBuilder();
				String str;
				while (null != (str = br.readLine())) {
					sb.append(str);
				}
				str = sb.toString();
				// System.out.println(str);
				StringTokenizer st = new StringTokenizer(str, ";\n");
				while (st.hasMoreTokens()) {
					String token = st.nextToken();
					Helper.localMysqlExecute(token);
				}
			} else {
				Debug.log("database script: appdata/player_db.sql is not found in the software package", Debug.ERROR_LEVEL);
			}
		} catch (IOException ex) {
			System.err.println("Error while reading CSV file.");
		} catch (SQLException ex) {
			Debug.showDatabaseAccessErrorAlert(Initializer.class.getName() + "::" + "createPlayerDatabase(): " + ex.getMessage());
		} finally {
			try {
				if (s != null) {
					s.close();
				}
			} catch (SQLException ex) {
				System.err.println("Error closing SQL conection.");
			}
		}
	}

	public static void executeSqlScriptsFromFolder() {
		File sqlFolder = new File(Settings.getSqlDirectory());
		if (sqlFolder.isDirectory()) {
			for (File sqlFile : sqlFolder.listFiles()) {
				if (Helper.getFileExtension(sqlFile.getPath()).equalsIgnoreCase("sql")) {
					SQLFileParser p = new SQLFileParser(sqlFile);
					p.executeStatements();
					boolean res = sqlFile.delete();
					// System.out.println("Delete File successful: " + res);
				}
			}
		}
	}

	public static void inputCsvFilesToDatabase() {
		File csvFolder = new File(Settings.getCsvDirectory());
		if (csvFolder.isDirectory()) {
			CSVToMysqlConverter c = new CSVToMysqlConverter();
			for (File csvFile : csvFolder.listFiles()) {
				if (Helper.getFileExtension(csvFile.getPath()).equalsIgnoreCase("csv")) {
					try {
						// convert CSV rows to SQL statements and execute them.
						c.put(csvFile.getPath(), Helper.getFileNameWithoutExtension(csvFile.getName()));
					} catch (IOException e) {
						Debug.log("Error Reading CSV File " + csvFile.getPath(), Debug.ERROR_LEVEL);
					}
					boolean res = csvFile.delete();
				}
			}
		}
	}

	/**
	 * This function is used to download files one by one
	 * 
	 */
	public static boolean downloadContents() {
		boolean rtr = true;
		List<Content> contents = new ArrayList<Content>();
		String sql = "SELECT * FROM content WHERE has_update = 1;";
		Connection conn = Settings.getLocalDatabaseConnection();
		ResultSet result = null;
		Statement stmt = null;
		Downloader downloader = Downloader.createDownloader();

		// find all the contents to be updated.
		try {
			int numResults = Helper.getNumberOfResults(sql);
			if (numResults <= 0) {
				return true;
			}
			stmt = conn.createStatement();
			result = stmt.executeQuery(sql);
			result.beforeFirst();
			while (result.next() == true) {
				Content c = new Content(result.getInt("content_id"));
				contents.add(c);
			}

		} catch (SQLException ex) {
			System.err.println("Initializer:downloadContent(): " + ex.getMessage());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException ex) {
				System.err.println("Error closing SQL conection.");
			}

		}
		// update contents
		// System.out.println(contents.size());
		synchronized (downloader) {
			for (Content c : contents) {
				String remoteFile = null;
				if (c.getServer() == Downloader.DIGITAL_MARKETING_BOX_SERVER) {
					remoteFile = Settings.getBoxId() + "/images/" + c.getContent();
				} else {
					remoteFile = c.getPath();
				}
				if (!Downloader.shouldDownload(Downloader.getHttpAddress(remoteFile, c.getServer()))) {
					System.out.println("this file has reached fail limit, don't download it again.");
					continue;
				}
				String local = Settings.getResourceDirectory() + c.getDirectory() + "/" + c.getContent();
				boolean downloaded = downloader.download(remoteFile, local, c.getServer());
				Downloader.manageFailureList(Downloader.getHttpAddress(remoteFile, c.getServer()), downloaded);
				rtr = downloaded && rtr;
				if (downloaded) {
					c.setHasUpdate(false);
				} else {
					UpdateChecker.setNeedDownloadContent(true);
				}
			}
		}
		return rtr;
	}

	public static void createDirs() {
		List<File> flist = new ArrayList<File>();
		flist.add(new File(Settings.getResourceDirectory()));
		flist.add(new File(Settings.getImageDirectory()));
		flist.add(new File(Settings.getVideoDirectory()));
		flist.add(new File(Settings.getScriptDirectory()));
		flist.add(new File(Settings.getCsvDirectory()));
		flist.add(new File(Settings.getNewPlayerDirectory()));
		flist.add(new File(Settings.getSqlDirectory()));
		flist.add(new File(Settings.getHtmlDirectory()));

		for (File file : flist) {
			file.mkdirs();
		}

	}

	public static void runCommandLineScripts() {
		CommandParser parser = new CommandParser();
		parser.executeAllCommands();
	}

	public static void main(String[] args) {
		// System.out.println(downloadContents());
		downloadContents();
	}

}
